package quanlyhochieu.truyvanhinhanh;

import java.io.BufferedInputStream;
import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;


import utils.DateUtils;
import utils.UtilsOnScopes;

import dbutil.ConnectDB;

public class BeanTruyVanHinhAnh {
	private String error;
	private java.util.Date  starDate;
	private java.util.Date endDate;
	private String nuocden;
	private String path ="/qlhochieu/truyvanhinhanh/emptyBieuMau.xhtml" ;
	private List<Table> list;
	
	public BeanTruyVanHinhAnh() {
	}
	
	
	/**************************************
	 * Method name		: checkValue
	 * Return type		: boolean
	 * Decription		:Kiem tra du lieu dau vao
	 * Created date		: Jun 18, 2008
	 * Author			: Honglk
	 **************************************/
	public boolean checkValue( java.util.Date star,java.util.Date  end){
		boolean result = true;
		if(star == null || star.equals("")){
			this.setError("Bạn chưa chọn ngày bắt đầu !");
			return result = false;
		}
		else if(end == null || end.equals("")){
			this.setError("Bạn chưa chọn ngày kết thúc !");
		  return result = false;
		}
		else if(star.compareTo(end) > 0){
			this.setError("Ngày kết thúc lớn hơn ngày bắt đầu !");
			return result = false;
			
		}
		return result;
	}
	
	
	/**************************************
	 * Method name		: delete
	 * Return type		: void
	 * Description		:xoa file hinh cu
	 * Created date		: Jul 27, 2008
	 * Author			: Honglk
	 **************************************/
	public void delete() {
		 String realPath = UtilsOnScopes.getSession().getServletContext().getRealPath("qlhochieu/truyvanhinhanh/image/");
		try {
			File file = new File(realPath);
			if (file.isDirectory()) {
				File[] files = file.listFiles();
				for (File file2 : files) {
					if (file2.isFile()) {
						System.out.println(file2.delete());
						file2.delete();
					}
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	
	/**************************************
	 * Method name		: viewImage
	 * Return type		: String
	 * Description		:Truy van lay len hinh anh
	 * Created date		: Aug 16, 2008
	 * Author			: Honglk
	 **************************************/
	public  String viewImage() throws IOException {
		ConnectDB.connectDB();
		this.setError("");
		if(this.checkValue(this.starDate, this.endDate)== false){
			return "";
		}
		if(this.nuocden == null || this.nuocden.equals("-1")){
			this.setError("Chưa nhập nước đến !");
			return "";
		}
		this.delete();
		
		Date starDateSQL = DateUtils.utilDateToSqlDate(this.getStarDate());
		Date endDateSQL = DateUtils.utilDateToSqlDate(this.getEndDate());
		List<Table> list = new ArrayList<Table>();
		byte[] arrData = new byte[10024];
		String SQL = "SELECT * FROM canhan A INNER JOIN dsnguoidi B ON A.cmnd = B.cmnd " +
					" INNER JOIN doanra C ON B.iddoanra = C.madoan " +
					" WHERE C.nuocden LIKE '"+this.nuocden+"' " +
					" AND (C.ngaydi BETWEEN ' " + starDateSQL + "' AND '"+ endDateSQL + "') " +
					" AND B.trangthaidi =true AND C.flagdelete = 0";
//					+" AND A.cmnd= 10000042";
		System.out.println(SQL);
		//int name_temp = 0;
		try {
			 ResultSet resultSet = ConnectDB.statement.executeQuery(SQL);
			 String realPath = UtilsOnScopes.getSession().getServletContext().getRealPath(
					 			"qlhochieu/truyvanhinhanh/image/");
			int canRead = 0;
			String cmnd ="";
			int stt = 0;
			FileOutputStream fos = null;
			BufferedOutputStream bos = null;
			while (resultSet.next()) {
				stt =stt+1;
				String ten = resultSet.getString("ten") ;
				String namsinhTemp = "<b> Năm sinh: </b> "+DateUtils.formatDate(resultSet.getString("namsinh")==null?"":resultSet.getString("namsinh"));
				String gioitinh = resultSet.getBoolean("gioitinh")==true?"Nam":"Nữ";
				//String coquan = "Cơ quan :"+resultSet.getString("coquan");
				String dangvien = resultSet.getBoolean("dangvien")==true?"Đảng viên":"Không";
				String chucvu = "<b> Chức vụ: </b> "+resultSet.getString("chucvu");
				cmnd = resultSet.getString("cmnd");
				String infoPassport = this.getInfoPassport(resultSet.getString("cmnd"));
				Table table = new Table();
				File fileImage = new File(realPath+"/"+cmnd+".jpg");
//				File fileImage = new File("c:"+"/"+cmnd+".jpg");
				fos = new FileOutputStream(fileImage);
			    bos = new BufferedOutputStream(fos);
				BufferedInputStream bis = null;
				bis = new BufferedInputStream(resultSet.getBinaryStream("anh"));
				System.out.println("duong dan :"+realPath);
				
				while ((canRead = bis.read(arrData)) != -1) {
					bos.write(arrData, 0, canRead);
				}
				
				
				String info ="<table style='font-size:9pt' border='0' align='right'>" +
						"<tr><td> <li><b>Họ tên: "+ten +"</b></li> </td></tr>" +
						"<p/>" +
						"<tr><td><li>"+namsinhTemp +"</li></td></tr>" +
						"<p/>" +
						"<tr><td><li> <b>Số CMND:</b> " +cmnd+ "</li></td></tr>"+
						"<p/>"+
						"<tr><td><li><b> Giới tính:</b> "+gioitinh + "</li></td></tr>"+
						"<p/>" +
						"<tr><td><li>"+chucvu+"</li></td></tr>" +
						"<p/>" +
						"<tr><td><li><b>Đảng viên:</b> "+dangvien+"</li></td></tr>" +
						infoPassport +
						"</table>"
						;
				table.setInfo(info);
				table.setPathImage("image/"+cmnd+".jpg");
				System.out.println("duong dan image:" +table.getPathImage());
				System.out.println("duong info:" +table.getInfo());
				table.setStt(String.valueOf(stt));
				list.add(table);
			}
			this.setList(list);
			if(this.getList() != null && this.getList().size()> 0){
				this.setPath("/qlhochieu/truyvanhinhanh/bieumau.xhtml");
			}else {
				this.setError("Không có dữ liệu !");
				return "";
			}
			bos.flush();
			bos.close();
			ConnectDB.connect.close();
			
		} catch (SQLException se) {
			se.printStackTrace();
			System.out.println(" SQL is invalid");
		}
		return "okTruyVanHinhAnh";
	}
	
	public  String viewImagetest() throws IOException {
		ConnectDB.connectDB();
		byte[] arrData = new byte[10024];
		String SQL = "SELECT * FROM canhan A LEFT JOIN dsnguoidi B ON A.cmnd = B.cmnd " +
		" LEFT JOIN doanra C ON B.iddoanra = C.madoan " +
		" WHERE A.cmnd = 10000042";
		System.out.println(SQL);
		//int name_temp = 0;
		try {
			ResultSet resultSet = ConnectDB.statement.executeQuery(SQL);
//			String realPath = UtilsOnScopes.getSession().getServletContext().getRealPath(
//					"qlhochieu/truyvanhinhanh/image/");
			int canRead = 0;
			String cmnd ="";
			int stt = 0;
			
			while (resultSet.next()) {
				stt =stt+1;
				String ten = resultSet.getString("ten") ;
				String namsinhTemp = "<b> Năm sinh: </b> "+DateUtils.formatDate(resultSet.getString("namsinh")==null?"":resultSet.getString("namsinh"));
				String gioitinh = resultSet.getBoolean("gioitinh")==true?"Nam":"Nữ";
				//String coquan = "Cơ quan :"+resultSet.getString("coquan");
				String dangvien = resultSet.getBoolean("dangvien")==true?"Đảng viên":"Không";
				String chucvu = "<b> Chức vụ: </b> "+resultSet.getString("chucvu");
				cmnd = resultSet.getString("cmnd");
				String infoPassport = this.getInfoPassport(resultSet.getString("cmnd"));
				Table table = new Table();
//				File fileImage = new File(realPath+"/"+cmnd+".jpg");
				File fileImage = new File("c:"+"/"+cmnd+".jpg");
				FileOutputStream fos = new FileOutputStream(fileImage);
				BufferedOutputStream bos = new BufferedOutputStream(fos);
				BufferedInputStream bis = null;
				bis = new BufferedInputStream(resultSet.getBinaryStream("anh"));
				while ((canRead = bis.read(arrData)) != -1) {
					bos.write(arrData, 0, canRead);
				}
				bos.flush();
				bos.close();
				ConnectDB.connect.close();
				
				
				
			}
			
		} catch (SQLException se) {
			se.printStackTrace();
			System.out.println(" SQL is invalid");
		}
		return "okTruyVanHinhAnh";
	}
	
	
	/**************************************
	 * Method name		: getInfoPassport
	 * Return type		: String
	 * Description		:lay ra danh sach HC tuong ung voi nguoi
	 * Created date		: Aug 16, 2008
	 * Author			: Honglk
	 **************************************/
	public String getInfoPassport(String cmnd) {
		String SQL = "SELECT * FROM hochieu hc WHERE hc.cmnd ='" + cmnd + "'";
		System.out.println(SQL);
		ArrayList<HashMap<String, Object>> array = ConnectDB
				.getDataTypeList(SQL);
		StringBuffer buffer = new StringBuffer();
		try {
			if (array.size() > 0) {
				HashMap<String, Object> hash = new HashMap<String, Object>();
				for (int i = 0; i < array.size(); i++) {
					hash = (HashMap<String, Object>) array.get(i);
					String loaiHC = hash.get("loaihc").toString();
					String soHC = hash.get("sohc").toString();
					String ngaycap = DateUtils.formatDate(hash.get("ngaycap").toString());
					String ngayhethan = DateUtils.formatDate(hash.get("ngayhethan").toString());
					String noicap = hash.get("noicap").toString();
					
					buffer.append("<tr><td><li><b>Số HC:</b> "+soHC+"</li></td></tr><p/>");
					buffer.append("<tr><td><li><b>Loại HC:</b>" +loaiHC+"</li></td></tr><p/>");
					buffer.append("<tr><td><li><b>Nơi cấp:</b> "+noicap+"</li></td></tr><p/>");
					buffer.append("<tr><td><li><b>Ngày cấp:</b> "+ngaycap+"</li></td></tr><p/>");
					buffer.append("<tr><td><li><b>Ngày hết hạn:</b> "+ngayhethan+"</li></td></tr><p/>");
				}
			}
			ConnectDB.connect.close();
		} catch (Exception e) {

		}
		return buffer.toString();
	}
	
	/**************************************
	 * Method name		: reset
	 * Return type		: String
	 * Description		:
	 * Created date		: Aug 16, 2008
	 * Author			: Honglk
	 **************************************/
	public String reset() {
		this.delete();
		UtilsOnScopes.getSession().removeAttribute("beanTruyVanHinhAnh");
		return "okTruyVanHinhAnh";
	}
	
	
	public void importImage(){
		// dua hinh xuong csdl
		ConnectDB.connectDB();
		//Doi ngay tu uitl sang ngay cua sql
		java.sql.Date  dateSQL = DateUtils.utilDateToSqlDate(new java.util.Date(1978,12,3));
		//end
		
//		 String sql ="CREATE TABLE canhan(cmnd VARCHAR(60) primary key,ten VARCHAR(60),namsinh VARCHAR(60), " +
//		 "coquan VARCHAR(60),gioitinh boolean,dangvien boolean,chucvu VARCHAR(60),anh bytea)";
//		 try {
//			ConnectDB.statement.execute(sql);
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
		String SQL = "INSERT INTO canhan(cmnd,ten,namsinh,coquan,gioitinh,dangvien,chucvu,anh) " +
					  " VALUES(?,?,?,?,?,?,?,?)";
//		String SQL = "update canhan set anh = ? where cmnd = 10000010";
		String pathFileImage = "E:\\HOC KY 8\\DE TAI TOT NGHIEP\\image_ca nhan\\NguyenVietThang.bmp";
		File fileImage = null;
		FileInputStream fis = null;
		BufferedInputStream bis = null;
		try {
			fileImage = new File(pathFileImage);
			fis = new FileInputStream(fileImage);
			bis = new BufferedInputStream(fis);
		} catch (FileNotFoundException exFile) {
			exFile.printStackTrace();
			return;
		}
		try {
			// statement.executeUpdate(sql);
			
			PreparedStatement pre = ConnectDB.connect.prepareStatement(SQL);
			pre.setString(1, "10000042");
			pre.setString(2, "Lê Văn Lĩnh");
			pre.setDate(3, dateSQL);
			pre.setBoolean(5, true);
			pre.setString(4,"So ngoai vu" );
			pre.setBinaryStream(8, bis, (int) fileImage.length());
			pre.setBoolean(6, true);
			pre.setString(7, "Nhân viên");
			pre.executeUpdate();
		} catch (SQLException se) {
			se.printStackTrace();
			System.out.println(" SQL is invalid");
		}

	//End
	}
	
	public String getPath() {
		return path;
	}


	public void setPath(String path) {
		this.path = path;
	}


	public List<Table> getList() {
		return list;
	}


	public void setList(List<Table> list) {
		this.list = list;
	}


	public String getError() {
		return error;
	}
	public void setError(String error) {
		this.error = error;
	}
	public java.util.Date getStarDate() {
		return starDate;
	}
	public void setStarDate(java.util.Date starDate) {
		this.starDate = starDate;
	}
	public java.util.Date getEndDate() {
		return endDate;
	}
	public void setEndDate(java.util.Date endDate) {
		this.endDate = endDate;
	}
	public String getNuocden() {
		return nuocden;
	}
	public void setNuocden(String nuocden) {
		this.nuocden = nuocden;
	}
	public static void main(String[] args) {
		BeanTruyVanHinhAnh ex = new BeanTruyVanHinhAnh();
//		ex.delete();
		try {
			ex.viewImagetest();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

//		ex.importImage();
	}
	
}
